package edu.nctu.csie.jichang.database.model.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.model.cell.ColumnFullInfo;
import edu.nctu.csie.jichang.database.util.DBException;
import edu.nctu.csie.jichang.database.util.StringUtil;


public class DBConnSQLServer extends AbstractDBConnection{

	private static final Logger LOG = LoggerFactory.getLogger(DBConnSQLServer.class);
	
	protected DBConnSQLServer(LoginInfo pLoginUserInfo) {
		super(pLoginUserInfo);
		SCHEMA_PATTERN = "dbo";
		UNIQUE_COLUMN_TYPE = 3;
	}

	static{
		try{
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
		}catch (Exception e) {
			LOG.error("",e);
		}
	}
	
	public void createConnection() {
		String tUrl = StringUtil.EMPTY;
		try {
			if(conn == null || conn.isClosed()){
				String tDBName   = loginInfo.getSchema().trim();
				String tDBHost   = loginInfo.getDatabaseInfo().getHost().trim();
				String tDBPort   = loginInfo.getDatabaseInfo().getPort().trim();
				String tUserName = loginInfo.getDatabaseInfo().getName().trim();
				String tUserPwd  = loginInfo.getDatabaseInfo().getPassword().trim();
				tUrl = "jdbc:jtds:sqlserver://"+tDBHost+":"+tDBPort+"/"+tDBName;
				conn = DriverManager.getConnection("jdbc:jtds:sqlserver://"+tDBHost+":"+tDBPort+"/"+tDBName,tUserName,tUserPwd);
			}
		} catch (Exception e) {
			throw new DBException(tUrl, e);
		}
	}

	protected List<ColumnFullInfo>  initNullableName(String pTableName, List<ColumnFullInfo> pOut) {
		ResultSet tRS = null;
		String tSQL = 
		"select c.name columnname,d.name name " +
		"from sys.default_constraints d,sys.columns c " +
		"where d.parent_object_id = c.object_id and d.parent_column_id = c.column_id and OBJECT_NAME(d.parent_object_id) = '"+pTableName+"'";
		Statement tStatement = null;
		try {
			tStatement = getStatement();
			tRS = getQueryResult(tStatement,tSQL);
			while(tRS.next()){
				for (ColumnFullInfo c : pOut) {
					if(tRS.getString("columnname").equalsIgnoreCase(c.getName())){
						 c.setNullableName(tRS.getString("name"));
					}
				}
			}
		} catch (Exception e) {
			LOG.error("",e);
		}finally{
			close(tRS,tStatement);
		}
		return pOut;
	}
	
	public void dropDatabase() {
		Connection tConn  = null;
		Statement  tState = null;
		try {
			String tDBName = loginInfo.getSchema().trim();
			tConn = createManagerConnection();
			tState = tConn.createStatement();
			String tSQL = "if exists (select * from master.dbo.sysdatabases where name = '"+tDBName+"') DROP DATABASE "+tDBName;
			tState.executeUpdate(tSQL);
			LOG.info("Drop database " + tDBName);
		}catch (Exception e) {
			throw new DBException(e);
		}finally{
			close(tState);
			close(tConn);
		}
	}

	public void dropAndCreateDatabase() {
		Connection tConn  = null;
		Statement  tState = null;
		try {
			String tDBName = loginInfo.getSchema().trim();
			tConn  = createManagerConnection();
			tState = tConn.createStatement();
			String tSQL = "if exists (select * from master.dbo.sysdatabases where name = '"+tDBName+"') DROP DATABASE "+tDBName;
			tState.executeUpdate(tSQL);
			tSQL = "CREATE DATABASE "+tDBName;
			tState.executeUpdate(tSQL);
			LOG.info("CREATE database " + tDBName);
		}catch (Exception e) {
			throw new DBException(e);
		}finally{
			close(tState);
			close(tConn);
		}
	}
	
	private Connection createManagerConnection() throws Exception {
		String tDBHost   = loginInfo.getDatabaseInfo().getHost().trim();
		String tDBPort   = loginInfo.getDatabaseInfo().getPort().trim();
		String tUserName = loginInfo.getDatabaseInfo().getName().trim();
		String tUserPwd  = loginInfo.getDatabaseInfo().getPassword().trim();
		return DriverManager.getConnection("jdbc:jtds:sqlserver://"+tDBHost+":"+tDBPort+"/",tUserName,tUserPwd);
	}

}